package com.fckbug.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.fckbug.dao.UserDAO;
import com.fckbug.vos.Acc;

public class UserDAOImpl extends HibernateDaoSupport implements UserDAO {
	private static final String QUERY_USER = "SELECT ID,PWD,NICKNAME FROM USERS WHERE USERNAME=:USERNAME";
	private static final String QUERY_USER_BY_ID = "SELECT ID,USERNAME,NICKNAME,EMAIL,REALNAME,LOCATION,BIRTHDAY,COMPANY FROM USERS WHERE ID=:ID";
	private static final String LIST_TOP100_USER = "SELECT USERID,ROUND(SUM(CNT)) TOTAL FROM (SELECT Q.USERID USERID,SUM(QT.FAVORITE)+SUM(QT.MARK)+SUM(QT.VIEW)/50 CNT FROM QUESTION Q,QTIME QT WHERE Q.ID=QT.QID GROUP BY Q.USERID UNION ALL SELECT A.USERID USERID,SUM(AT.MARK) CNT FROM ANSWER A,ATIME AT WHERE A.ID=AT.AID GROUP BY A.USERID) AS TB GROUP BY USERID ORDER BY TOTAL DESC LIMIT 0,100";
	private static final String REG_USER = "INSERT INTO USERS(USERNAME,PWD,NICKNAME) VALUES(:USERNAME,:PWD,:NICKNAME)";
	private static final String UPDATE_USER = "UPDATE USERS SET NICKNAME=:NICKNAME,EMAIL=:EMAIL,REALNAME=:REALNAME,LOCATION=:LOCATION,BIRTHDAY=:BIRTHDAY,COMPANY=:COMPANY WHERE ID=:ID";
	
	public List getUserByUsername(final String username) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_USER);
					query.setString("USERNAME", username);
					return query.list();
				}
			}
		);
	}
	
	@Override
	public List getUserByUserId(final int userId) {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(QUERY_USER_BY_ID);
					query.setInteger("ID", userId);
					return query.list();
				}
			}
		);
	}
	
	public Integer saveRegUser(final String username, final String pwd, final String nickname) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(REG_USER);
					query.setString("USERNAME", username);
					query.setString("PWD", pwd);
					query.setString("NICKNAME", nickname);
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public Integer updateUser(final Acc acc) {
		return (Integer)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(UPDATE_USER);
					query.setString("NICKNAME", acc.getNickName());
					query.setString("EMAIL", acc.getEmail());
					query.setString("REALNAME", acc.getRealName());
					query.setString("COMPANY", acc.getCompany());
					query.setString("LOCATION", acc.getLocation());
					query.setString("BIRTHDAY", acc.getBirthday());
					query.setInteger("ID", acc.getId());
					return query.executeUpdate();
				}
			}
		);
	}

	@Override
	public List listTopUsers() {
		return (List)this.getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					SQLQuery query = session.createSQLQuery(LIST_TOP100_USER);
					return query.list();
				}
			}
		);
	}

}
